*****************************************************************************
*For:	"The White/Black Educational Gap, Stalled Progress, and the Long-term
*		Consequences of the Emergence of Crack Cocaine Markets"
*By: 	William N. Evans, Craig Garthwaite and Timothy J. Moore
*****************************************************************************

*****************************************************************************
************* CODE FOR CALCULATING MURDER RATES FOR FIGURE 5 ****************
******** ESTIMATING WHEN CRACK COCAINE ARRIVED IN DIFFERENT MSA/STATES ******
*****************************************************************************

* These are constructed from the mortality analysis done in SAS. Mortality rates 
* are merged with the crack introductions in the one dataset and analyzed here.

#delim cr
set more off
set varabbrev off
pause on
graph set ps logo off

capture log close
set linesize 200
set logtype text
log using ./mortalityresults.log , replace

clear all
estimates clear

*****************************************************************
*** Figure 5 - Murder Rates Before and After the Introduction ***
***** of Crack Markets in 57 Large MSAs, By Race and Sex, *******
************ Multiple Cause of Death Data 1973-2000 *************
*****************************************************************

* Create years in relation to arrival of crack
use msa_murder_data.dta, clear
sort msafips year
gen yearcrack=year-when_crack_arrives
keep if -8<=yearcrack & yearcrack<=8
sort yearcrack
save temp.dta, replace

*************************************
*** Panel A - Black males, by age ***
*************************************

preserve
collapse b_m_15_24_mur b_m_15_24_pop b_m_25_39_mur b_m_25_39_pop ///
	b_m_40plus_mur b_m_40plus_pop, by(yearcrack)
gen murder_rate15_24=b_m_15_24_mur*100000/b_m_15_24_pop
gen murder_rate25_39=b_m_25_39_mur*100000/b_m_25_39_pop
gen murder_rate40plus=b_m_40plus_mur*100000/b_m_40plus_pop
export excel using murder_rates_black_males, replace
restore


*************************************
*** Panel B - White males, by age ***
*************************************

preserve
collapse w_m_15_24_mur w_m_15_24_pop w_m_25_39_mur w_m_25_39_pop ///
	w_m_40plus_mur w_m_40plus_pop, by(yearcrack)
gen murder_rate15_24=w_m_15_24_mur*100000/w_m_15_24_pop
gen murder_rate25_39=w_m_25_39_mur*100000/w_m_25_39_pop
gen murder_rate40plus=w_m_40plus_mur*100000/w_m_40plus_pop
export excel using murder_rates_white_males, replace
restore


***************************************
*** Panel C - Black females, by age ***
***************************************

preserve
collapse b_f_15_24_mur b_f_15_24_pop b_f_25_39_mur b_f_25_39_pop ///
	b_f_40plus_mur b_f_40plus_pop, by(yearcrack)
gen murder_rate15_24=b_f_15_24_mur*100000/b_f_15_24_pop
gen murder_rate25_39=b_f_25_39_mur*100000/b_f_25_39_pop
gen murder_rate40plus=b_f_40plus_mur*100000/b_f_40plus_pop
export excel using murder_rates_black_females, replace
restore


***************************************
*** Panel D - White females, by age ***
***************************************

preserve
collapse w_m_15_24_mur w_f_15_24_pop w_f_25_39_mur w_f_25_39_pop ///
	w_f_40plus_mur w_f_40plus_pop, by(yearcrack)
gen murder_rate15_24=w_f_15_24_mur*100000/w_f_15_24_pop
gen murder_rate25_39=w_f_25_39_mur*100000/w_f_25_39_pop
gen murder_rate40plus=w_f_40plus_mur*100000/w_f_40plus_pop
export excel using murder_rates_white_females, replace
restore

